﻿CREATE PROCEDURE [dbo].[CalcZakaz]
(
	@DepartmentId int,										-- код подразделения
	@PeriodBegin smalldatetime,
	@PeriodEnd smalldatetime,
	@ForDaysPeek int = 1,									--количество дней в сплеске
	@PeekCoeff int = 3,										--количество превышения продаж для всплеска
	@UseThirdCategory bit = 0,						--расчитывать всплески для 3-й категории товаров
	@StoreCoef real = 0.03,								--процент расходов по хранению на складе
	@IputCost real,												--стоимость прихода (руб)
	@PriceForRound real = 80.0,						--цена для расчета округлённого количества (руб)
	@WithAggregate bit = 0,								--=1- расчет с агрегированием товаров у которых одинаковые AssortmentId, RetailPack (точнее DefekturaAggregate = 1)
	@DaySaleSum [decimal](19, 2) OUTPUT,	--средняя сумма продаж
	@UslStockDays real OUTPUT,						--запас обычный в днях
	@PeekStockDays real OUTPUT						--запас по всплеску в днях
)
AS
/* Черноуосов ОП 13.07.06 Вычисление заказов */
DECLARE @intRet integer, @intErrCode int, @strAction varchar(50)
DECLARE @intDuration integer

DECLARE @tblSalePeriod TABLE (GoodsId int NOT NULL, 
SaleQntty real NOT NULL, 
SaleSum [decimal](19, 2) NULL,
SaleDayCount int NOT NULL,		--число дней продаж за период
SaleAvrg real NULL, 
SaleSquare real NULL, 

SaleDayUhod real NULL,				--средний уход по обычным продажам в день
SaleDisp real NULL,						--дисперсию по обычным продажам

[Peek] [bit] NULL,
ManualCalc bit NULL,					--ручной расчет заказа
OrdinaryCalc bit NULL,				--обычный расчет заказа без учета всплесков
ProbValue real default(0.0),	--вероятность отказа покупателем
ZO real NULL default(0.0),		--запасы по обычным продажам
ZV real NULL default(0.0),		--запасы по всплескам
Rest real,										--остаток на конец периода
AvgSalePrice real,						--средняя цена по обычным продажи
PeekAvgPrice real,						--средняя цена по всплескам
Duration int,									--сумма времени поиска и времени доставки
DaysForCalc int NULL,
ZU real NULL,									--расчитанный заказ по обычным товарам
PeekStockItem [decimal](19, 2) NULL default(0.0), --Параметр для расчета запаса  по всплескам
UslStockItem [decimal](19, 2) NULL,								--Параметр для расчета запаса  по обычным продажам,
AvgDaySaleSum [decimal](19, 2) NULL default(0.0), --Средняя сумма продаж в день по всем продажам
FullDays int NULL,						--кол-во дней присутствия по обычным продажи
FullDaysAll int NULL					--кол-во дней присутствия по всем продажам
PRIMARY KEY (	GoodsId) )

--таблица содержит данные которые возвращяются клиенту
DECLARE @tblResultByGoods TABLE (GoodsId int NOT NULL, 
TtlSaleQntty real NULL, 
TtlSaleSum [decimal](19, 2) NULL,
TtlSaleDayCount int NULL,						--количество дней продаж ВСЕГО
TtlFullDays int NULL,								--число дней присутствия
AvgSalePrice [decimal](19, 2) NULL, --Средняя цена продажи
--обычные продажи
UslSaleDayUhod real NULL, 
UslSaleDisp real NULL, 
UslAvgSalePrice real, 						--средняя цена продажи
UslSaleQntty int NULL, 						--сумма продаж в штуках
UslSaleSum [decimal](19, 2) NULL, --сумма продаж в деньгах
UslSaleDayCount int NULL,					--количество дней продаж по обычным продажам
--продажи со всплесками
PeekDayUhod real NULL,							--средний уход по всплескам в день
PeekDisp real NULL,									--дисперсию по всплескам
PeekSaleQntty real NULL, 						--сумма продаж в штуках
PeekSaleSum [decimal](19, 2) NULL,  --сумма продаж в деньгах
PeekSaleDayCount int NULL,					--количество дней продаж по всплескам
PeekCount int NULL,
ManualCalc bit NULL, 		--ручной расчет заказа
OrdinaryCalc bit NULL, 	--обычный расчет заказа без учета всплесков
ZV real NULL,						--запасы по всплескам
ZO real NULL,						--запасы по обычным продажам
TtlRest int,						--остаток на конец периода
--ЗАКАЗ
ZU int NULL,										--расчитанный заказ 
DaysForCalc int NULL,
MarkUp real NULL  default(20.0), --аптечная наценка
PackQntty int NULL default(1),
Remainder int NULL,							--остаток при делении заказанногого кол-ва на кол-во в заводской упаковке
DeltaQntty real NULL,						--вспомогательный параметр
ZURounded int NULL, 							--округлённый заказ с учетом кол-ва в заводской упаковке
LastDaySale datetime null				--Добавлено Евгений Веприков, последний день продажи плюс 1 день 23.12.2006
PRIMARY KEY (	GoodsId) )

SET NOCOUNT ON
--Таблица для всплесков. Заполняется в процедуре CalcSalePeek
CREATE TABLE #SalePeek
(
	[GoodsId] [int] NOT NULL ,
	[DocDate] smalldatetime NOT NULL,
	[SaleQntty] real NULL,
	[SaleSum] [decimal](19, 2) NULL,  --сумма продаж в деньгах
	[SaleDayCount] [int] NULL default(1),
	[Peek] [bit] NULL default(0),
	[PeekSale] real NULL,							--сумма продаж в всплеске в штуках
	[PeekNmbr] int NULL,							--номер всплеска
	[PrevSale] real NULL default(0.0),	--сумма продаж в пределах диапазона поиска всплеска исключая последний день в штуках
	CONSTRAINT [PK_Sales] PRIMARY KEY  CLUSTERED 
	(
		[GoodsId], [DocDate]
	) 
)

--Таблица для оборотов по всплескам. Заполняется в процедуре CalcSalePeek
CREATE TABLE #PeekPeriod
(
	GoodsId int NOT NULL,
	SaleQntty real NOT NULL, 
	SaleDayCount int NOT NULL,
	FullDays int NULL,									--число дней присутствия
	PeekSale real NULL, 
	PeekCount int NULL,
	PeekDayUhod real NULL,							--средний уход по всплескам в день
	PeekDisp real NULL,									--дисперсию по всплескам
	ManualCalc bit NULL,								--ручной расчет заказа
	OrdinaryCalc bit NULL,							--обычный расчет заказа без учета всплесков
	MarkUp real NULL default(2.0),
	ZV real NULL,
	PeekSaleSum [decimal](19, 2) NULL,  --сумма продаж в деньгах
	PeekSaleDayCount int NULL,					--количество дней продаж 
	AvgSalePrice [decimal](19, 2) NULL, --Средняя цена продажи по всплескам
	PeekStockItem [decimal](19, 2) NULL,--Параметр для расчета запаса  по всплескам
	CONSTRAINT [PK_PeekPeriod] PRIMARY KEY CLUSTERED 
	(
		[GoodsId]
	) 
)
--SaleQntty PeekDayUhod PeekDisp PeekSale ZV
EXEC @intRet = dbo.CalcSalePeek @DepartmentId, @PeriodBegin, @PeriodEnd, @ForDaysPeek, @PeekCoeff, @UseThirdCategory, @StoreCoef, @WithAggregate

--Расчет по обычным продажам (без всплесков)
CREATE TABLE #TGoodsFlow
(
	[GoodsId] [int] NOT NULL ,
	[DocDate] smalldatetime NULL,
	[Turnover] real NULL,
	[SaleQntty] real NULL,
	[SaleSum] [decimal](19, 2) NULL,
	[DayRest] real NULL,
	[FullDays] [int] NULL,
	[Peek] [bit] NULL DEFAULT (0)
)

INSERT INTO #TGoodsFlow (GoodsId, DocDate, Turnover, SaleQntty, SaleSum, DayRest, FullDays)
SELECT GoodsId, DocDate, Turnover, SaleQntty, SaleSum, DayRest, FullDays 
FROM dbo.NotEmptyDays(@DepartmentId, @PeriodBegin, @PeriodEnd, @WithAggregate)

CREATE INDEX [IX_TGoodsGoodsIdDocDate] ON #TGoodsFlow(GoodsId, DocDate) 

--отмечаем продажы, которые принадлежат всплескам
UPDATE D SET D.Peek = S.Peek
FROM #TGoodsFlow D INNER JOIN #SalePeek S ON
D.GoodsId = S.GoodsId AND D.DocDate = S.DocDate
--AND D.DocDate <= dateadd(day, @ForDaysPeek - 1, S.DocDate )

--все продажи за период. Определение параметров по обычным продажам 
INSERT INTO @tblSalePeriod (GoodsId, FullDays, SaleQntty, SaleDayCount, AvgSalePrice, SaleSum, FullDaysAll )
SELECT GoodsId, sum(FullDays) - sum(CASE WHEN Peek = 1 THEN 1 ELSE 0 END ), --вычитаем дни когда были всплески
sum(CASE WHEN Peek = 1 THEN 0 ELSE SaleQntty END ) AS SaleQntty,
count(CASE WHEN SaleQntty > 0 AND Peek = 0 THEN DocDate ELSE NULL END  ) AS SaleDayCount,
avg(CASE WHEN SaleQntty > 0 AND Peek = 0 THEN SaleSum / SaleQntty ELSE NULL END ) AS AvgSalePrice,  --
sum(CASE WHEN SaleQntty > 0 AND Peek = 0 THEN SaleSum ELSE 0.0 END  ) AS SaleSum,
sum(FullDays) AS FullDaysAll
FROM #TGoodsFlow 
WHERE DocDate >= @PeriodBegin 
GROUP BY GoodsId

DELETE FROM @tblSalePeriod WHERE FullDaysAll = 0
--определяем средний уход в день по обычнм продажам
UPDATE @tblSalePeriod SET SaleDayUhod = (1.0 * SaleQntty ) / FullDays
WHERE FullDays > 0

--вычисляем дисперсию по обычным продажам 
UPDATE T SET T.SaleDisp = CASE WHEN FullDays > 0 THEN SQRT( P.SaleDisp / (1.0 * FullDays ) ) ELSE 0.0 END
FROM @tblSalePeriod T INNER JOIN (
SELECT D.GoodsId, SUM( POWER( ( S.SaleQntty - D.SaleDayUhod ), 2 ) ) AS SaleDisp
FROM @tblSalePeriod D INNER JOIN #TGoodsFlow S ON D.GoodsId = S.GoodsId AND S.Peek = 0 AND S.DocDate >= @PeriodBegin  --AND S.SaleQntty > 0.0
GROUP BY  D.GoodsId
) P ON T.GoodsId = P.GoodsId 

--SELECT D.GoodsId, s.DocDate, S.SaleQntty - D.SaleDayUhod AS SaleDisp, S.SaleQntty , D.SaleDayUhod, D.SaleQntty, D.FullDays
--FROM @tblSalePeriod D INNER JOIN #TGoodsFlow S ON D.GoodsId = S.GoodsId AND S.Peek = 0 AND S.SaleQntty > 0 AND S.DocDate >= @PeriodBegin 
--определяем сумму времени поиска и времени доставки
SELECT @intDuration = DeliveryDuration FROM CO.Departments WHERE Id = @DepartmentId

UPDATE T SET T.Duration = @intDuration + IsNull(C.SeekDuration, 0)
FROM @tblSalePeriod T INNER JOIN PR.DefekturaGoods G ON T.GoodsId = G.GoodsId
INNER JOIN CA.DefekturaCategoriesVsDepartments C 
ON G.DefekturaCategoryId = C.DefekturaCategoryId AND C.DepartmentId = @DepartmentId AND C.ToDelete = 0

--расчет запаса по обычным продажам
UPDATE D SET D.ProbValue = CASE D.Duration
WHEN 1 THEN P.Value
WHEN 2 THEN P.Value2
WHEN 3 THEN P.Value3
WHEN 4 THEN P.Value4
ELSE P.Value5 END
FROM @tblSalePeriod D INNER JOIN PR.DefekturaGoods G ON D.GoodsId = G.GoodsId
INNER JOIN CO.DefekturaCategories C ON G.DefekturaCategoryId = C.Id AND C.ToDelete = 0
INNER JOIN CO.DefekturaProbabilyties P ON C.PrblCode = P.Code AND P.ToDelete = 0
--select * FROM @tblSalePeriod WHERE goodsid =912

UPDATE @tblSalePeriod SET 
ZO = IsNull( (SaleDayUhod + SaleDisp * ProbValue ) * Duration, 0.0) ,
UslStockItem = ( SaleDisp * ProbValue ) * AvgSalePrice * Duration

--добавление расчитанных данных по всплескам
UPDATE D SET D.ZV = ISNULL(S.ZV, 0.0), D.ManualCalc = S.ManualCalc, D.OrdinaryCalc = S.OrdinaryCalc, 
D.Peek = ( CASE WHEN S.ManualCalc IS NULL AND S.OrdinaryCalc IS NULL THEN 1 ELSE 0 END ),
D.PeekStockItem = ISNULL(S.PeekStockItem, 0.0),
D.PeekAvgPrice = S.AvgSalePrice
FROM @tblSalePeriod D INNER JOIN #PeekPeriod S ON D.GoodsId = S.GoodsId

--Определяем остатки на конечную дату расчета
UPDATE D SET D.Rest = S.Rest
FROM @tblSalePeriod D INNER JOIN (
SELECT P.GoodsId, sum(T.Turnover) As Rest
FROM @tblSalePeriod P INNER JOIN #TGoodsFlow T ON P.GoodsId = T.GoodsId
GROUP BY P.GoodsId
) S ON D.GoodsId = S.GoodsId

--Вычисляем промежуточный параметр
UPDATE D SET D.DaysForCalc = SQRT( (2.0 * @IputCost) / ( D.AvgSalePrice * D.SaleDayUhod * (@StoreCoef / 25.0) ) )
FROM @tblSalePeriod D --INNER JOIN dbo.Goods G ON D.GoodsId = G.Id

--расчет заказа по обычным товарам
UPDATE D SET D.DaysForCalc = CASE WHEN D.Duration > D.DaysForCalc THEN D.Duration ELSE D.DaysForCalc END,
--D.ZU = (D.SaleDayUhod + D.SaleDisp * D.ProbValue ) * D.DaysForCalc ,
D.ZU = IsNUll(D.SaleDayUhod * D.DaysForCalc + D.SaleDisp * D.ProbValue * D.Duration, 0.0) + (CASE WHEN D.ZV IS NULL THEN 0 ELSE D.ZV END ) ,
D.AvgDaySaleSum = IsNull(AvgSalePrice, 0.0) * SaleDayUhod * ( (1.0 * D.FullDays ) / (1.0 * D.FullDaysAll) )  + PeekStockItem
FROM @tblSalePeriod D 
--WHERE D.Rest < D.ZO + D.ZV

--агрегированная статистика по подразделению
SELECT @DaySaleSum = sum( AvgDaySaleSum ),
@UslStockDays =  ( sum( 1.5 * UslStockItem ) + sum( ( IsNull(AvgSalePrice, 0.0) * DaysForCalc * SaleDayUhod ) / 2.0 )  ) / sum( AvgDaySaleSum ),
@PeekStockDays =  sum( ZV * PeekAvgPrice ) / sum( AvgDaySaleSum )
FROM @tblSalePeriod --where SaleQntty > 0
--WHERE Rest < ZO + ZV OR ManualCalc IS NOT NULL

--select * FROM @tblSalePeriod WHERE goodsid =1078

INSERT INTO @tblResultByGoods ( GoodsId,
UslSaleDayUhod, UslSaleDisp, UslAvgSalePrice, UslSaleQntty, UslSaleSum, UslSaleDayCount,
ManualCalc, OrdinaryCalc, ZO, ZV, TtlRest, ZU, DaysForCalc )
SELECT GoodsId,
SaleDayUhod, SaleDisp, AvgSalePrice, SaleQntty, SaleSum, SaleDayCount, 
ManualCalc, OrdinaryCalc, ZO, ZV, Rest, round(ZU - Rest,0), 
DaysForCalc
FROM @tblSalePeriod
WHERE Rest < ZO + ZV OR ManualCalc IS NOT NULL

--select * from  @tblResultByGoods WHERE goodsId in (898, 183) 

--параметры по всем продажам
UPDATE D SET 
D.TtlSaleQntty		= S.TtlSaleQntty,
D.TtlSaleSum			= S.TtlSaleSum,
D.TtlSaleDayCount = S.TtlSaleDayCount,
D.TtlFullDays			= S.TtlFullDays,
D.AvgSalePrice		= S.AvgSalePrice
FROM @tblResultByGoods D INNER JOIN (
SELECT P.GoodsId, sum(T.SaleQntty) As TtlSaleQntty, 
sum(CASE WHEN T.SaleQntty = 0 THEN 0.0 ELSE T.SaleSum END) AS TtlSaleSum,
count(CASE WHEN T.SaleQntty = 0 THEN NULL ELSE T.DocDate END ) AS TtlSaleDayCount,
sum(FullDays) AS TtlFullDays,
avg(CASE WHEN T.SaleQntty = 0 THEN NULL ELSE T.SaleSum / T.SaleQntty END ) AS AvgSalePrice
FROM @tblResultByGoods P INNER JOIN #TGoodsFlow T ON P.GoodsId = T.GoodsId
WHERE T.DocDate >= @PeriodBegin 
GROUP BY P.GoodsId
) S ON D.GoodsId = S.GoodsId

--параметры по всплескам
UPDATE D SET 
D.PeekDayUhod				= S.PeekDayUhod,
D.PeekDisp					= S.PeekDisp,
D.PeekSaleQntty			= S.PeekSale,
D.PeekSaleSum				= S.PeekSaleSum,
D.PeekSaleDayCount	= S.SaleDayCount,
D.PeekCount					= S.PeekCount,
D.MarkUp						= S.MarkUp
FROM @tblResultByGoods D INNER JOIN #PeekPeriod S ON D.GoodsId = S.GoodsId

--определяем наценку
UPDATE S SET S.MarkUp = ( CASE WHEN IsNull(R.MarkUp,20) = 0 THEN 20 ELSE IsNull(R.MarkUp,20) END)
FROM @tblResultByGoods S INNER JOIN (--определение последней (средней) наценки 
	SELECT L.GoodsId, avg(RetailMarkup) AS MarkUp
	FROM (--вычисляем максимальную дату прихода по товарам
		SELECT T.GoodsId, max(D.DocDate) AS DocDate
		FROM @tblResultByGoods T INNER JOIN CD.InputLines I ON T.GoodsId = I.GoodsId
		INNER JOIN CA.Documents D ON I.DocId = D.Id
		WHERE D.DocDate <= @PeriodEnd
		GROUP BY T.GoodsId
	) L INNER JOIN CD.InputLines I ON L.GoodsId = I.GoodsId
	INNER JOIN CA.Documents D ON I.DocId = D.Id AND D.DocDate = L.DocDate
	GROUP BY L.GoodsId
) R ON S.GoodsId = R.GoodsId

--расчет заказа с учетом количества в упаковках поставщика
UPDATE T SET
T.PackQntty = G.WholesalePack,
T.ZU = CASE WHEN T.ManualCalc IS NOT NULL THEN NULL ELSE T.ZU END
FROM @tblResultByGoods T INNER JOIN CA.Goods G ON T.GoodsId = G.Id 

UPDATE T SET T.Remainder = T.ZU % T.PackQntty,  -- T.ZU - ( T.ZU / G.PackQntty ) * G.PackQntty,
T.DeltaQntty = (1.0 * T.PackQntty ) / ( 25.0 * T.MarkUp / ( 100.0 * @StoreCoef * DaysForCalc ) + 1.0 )
FROM @tblResultByGoods T
WHERE T.PackQntty > 0
--Округление для заводских упаковок
UPDATE T SET T.ZURounded = CASE WHEN T.Remainder = 0 THEN T.ZU ELSE
	CASE WHEN T.DeltaQntty < T.Remainder THEN T.ZU + T.PackQntty - T.Remainder 
	ELSE T.ZU - T.Remainder
	END
END
FROM @tblResultByGoods T --INNER JOIN dbo.Goods G ON T.GoodsId = G.Id
--Вычислем последний день продаж, Добавлено Евгений Веприков 23.12.2006
UPDATE T
SET T.LastDaySale = 
(SELECT top(1) DATEADD(day,1, CA.GoodsDocLines.MD)
	FROM CD.Parties INNER JOIN
	CA.GoodsDocLines ON CD.Parties.Id = CA.GoodsDocLines.PartyId INNER JOIN
	CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id
	WHERE     (CA.Documents.DocTypeId = 3) AND (CD.Parties.GoodsId = T.GoodsId)
	ORDER BY CA.GoodsDocLines.MD DESC)
FROM @tblResultByGoods T
	SET NOCOUNT OFF

SELECT @DepartmentId AS DepartmentId, T.* FROM @tblResultByGoods T
WHERE ZU > 0 OR ManualCalc IS NOT NULL

RETURN 0


